﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Oracle.ManagedDataAccess.Client;
using VswService_V7.Utility;

namespace VswService_V7.DataBase.Tables
{
    /// <summary>
    /// wp code
    /// </summary>
    public partial class RECIPE_INFO
    {
        /// <summary>
        /// fuzzy query recipe information
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="minRow"></param>
        /// <param name="maxRow"></param>
        /// <param name="totalPages"></param>
        /// <returns></returns>
        public static DataTable SelectRecipeByParameters(string sql, int minRow, int maxRow, out string totalPages)
        {
            try
            {
                string sqlR = sql + string.Format(" where rn>{0} and rn<={1}", minRow, maxRow);
                string sqlCount = string.Format("select count(*) from ({0})",
                   sql);
                DataTable Table = OracleHelper.Exct_QueryTable(sqlR);
                if (Table != null)
                {
                    int Pages = Convert.ToInt32(TASK_INFO.GetTableCount(sqlCount));
                    if (Pages % pageNum == 0)
                    {
                        totalPages = (Pages / pageNum).ToString();
                    }
                    else
                    {
                        totalPages = (Pages / pageNum + 1).ToString();
                    }
                    LogHelper.WriteLog("SelectRecipeByParameters Success");
                    return Table;
                }
                else
                {
                    totalPages = "";
                    return null; ;
                }

            }
            catch (Exception ex)
            {
                LogHelper.WriteLog("SelectRecipeByParameters", ex);
                throw ex;
            }
        }

        /// <summary>
        /// add a new recipe
        /// </summary>
        /// <param name="recipeTable"></param>
        /// <returns></returns>	
        public static DataTable Insert_Recipe(DataTable recipeTable)
        {
            try
            {
                string sql = string.Format("insert into {0} values('{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}','{12}','{13}','{14}','{15}','{16}','{17}','{18}','{19}','{20}','{21}','{22}','{23}','{24}','{25}','{26}','{27}','{28}','{29}','{30}','{31}','{32}','{33}','{34}','{35}','{36}','{37}','{38}','{39}','{40}','{41}','{42}','{43}','{44}','{45}','{46}','{47}','{48}','{49}','{50}','{51}','{52}')", tableName,
                   recipeTable.Rows[0][fd_recipeName].ToString(), recipeTable.Rows[0][fd_recipeNo].ToString(), recipeTable.Rows[0][fd_steelGrade].ToString(), recipeTable.Rows[0][fd_steelClass].ToString(), recipeTable.Rows[0][fd_stripThickness].ToString(), recipeTable.Rows[0][fd_stripWidth].ToString(), recipeTable.Rows[0][fd_pipeOd].ToString(),
                   recipeTable.Rows[0][fd_pipeThickness].ToString(), "0", recipeTable.Rows[0][fd_yeildStrength].ToString(), recipeTable.Rows[0][fd_accPrPressure_1].ToString(), recipeTable.Rows[0][fd_accPrPressure_2].ToString(), recipeTable.Rows[0][fd_emCuttingSpd].ToString(),
                   recipeTable.Rows[0][fd_emChipload].ToString(), recipeTable.Rows[0][fd_sa1Power].ToString(), recipeTable.Rows[0][fd_sa1Temp].ToString(), recipeTable.Rows[0][fd_sa1Height].ToString(), recipeTable.Rows[0][fd_sa1Freq].ToString(), recipeTable.Rows[0][fd_sa2Power].ToString(),
                   recipeTable.Rows[0][fd_sa2Temp].ToString(), recipeTable.Rows[0][fd_sa2Height].ToString(), recipeTable.Rows[0][fd_sa2Freq].ToString(), recipeTable.Rows[0][fd_sa3Power].ToString(), recipeTable.Rows[0][fd_sa3Temp].ToString(), recipeTable.Rows[0][fd_sa3Height].ToString(),
                   recipeTable.Rows[0][fd_sa3Freq].ToString(), recipeTable.Rows[0][fd_sa4Power].ToString(), recipeTable.Rows[0][fd_sa4Temp].ToString(), recipeTable.Rows[0][fd_sa4Height].ToString(), recipeTable.Rows[0][fd_sa4Freq].ToString(), recipeTable.Rows[0][fd_hfwLineSpeed].ToString(), recipeTable.Rows[0][fd_HfwPower].ToString(),
                   recipeTable.Rows[0][fd_HfwFreq].ToString(), recipeTable.Rows[0][fd_HfwVee_X].ToString(), recipeTable.Rows[0][fd_sustCw1].ToString(), recipeTable.Rows[0][fd_sustCw2].ToString(), recipeTable.Rows[0][fd_stripWdTarget].ToString(), recipeTable.Rows[0][fd_HfwMode].ToString(),
                   recipeTable.Rows[0][fd_HfwPreHeatPower].ToString(), recipeTable.Rows[0][fd_HfwTemperature].ToString(), recipeTable.Rows[0][fd_HfwSPCGain].ToString(), recipeTable.Rows[0][fd_HfwSPCSlope].ToString(), "0", "0",
                   "0", "0", "0", recipeTable.Rows[0][fd_createDate].ToString(), recipeTable.Rows[0][fd_modifyDate].ToString(), recipeTable.Rows[0][fd_recipeStatus].ToString(),
                   recipeTable.Rows[0][fd_creater].ToString(), recipeTable.Rows[0][fd_modifyUser].ToString());
                if (OracleHelper.Exct_NonQuery(sql) > 0)
                {

                    LogHelper.WriteLog("Insert_Recipe Success");
                    return recipeTable;
                }
                else
                {
                    return null;
                }
            }
            catch (Exception ex)
            {
                LogHelper.WriteLog("Insert_Recipe", ex);
                throw ex;
            }
        }

        /// <summary>
        /// edit recipe infromation
        /// </summary>
        /// <param name="recipeTable"></param>
        /// <returns></returns>
        public static DataTable Edit_Recipe(DataTable recipeTable)
        {
            try
            {
                string sql = string.Format("UPDATE {0} SET {1}='{2}', {3}='{4}', {5}='{6}', {7}='{8}',{9}='{10}', {11}='{12}',{13}='{14}', {15}='{16}', {17}='{18}', {19}='{20}',{21}='{22}', " +
                                    "{23}='{24}', {25}='{26}', {27}='{28}', {29}='{30}',{31}='{32}', {33}='{34}',{35}='{36}', {37}='{38}', {39}='{40}', {41}='{42}',{43}='{44}'," +
                                    "{45}='{46}', {47}='{48}', {49}='{50}', {51}='{52}',{53}='{54}', {55}='{56}',{57}='{58}', {59}='{60}', {61}='{62}', {63}='{64}',{65}='{66}'," +
                                    "{67}='{68}', {69}='{70}', {71}='{72}', {73}='{74}',{75}='{76}', {77}='{78}',{79}='{80}', {81}='{82}', {83}='{84}'," +
                                    "{85}='{86}', {87}='{88}', {89}='{90}', {91}='{92}',{93}='{94}', {95}='{96}',{97}='{98}', {99}='{100}',{101}='{102}' WHERE {103}='{104}'",
                                    tableName, fd_recipeName, recipeTable.Rows[0][fd_recipeName].ToString(), fd_steelGrade, recipeTable.Rows[0][fd_steelGrade].ToString(), fd_steelClass, recipeTable.Rows[0][fd_steelClass].ToString(), fd_stripThickness, recipeTable.Rows[0][fd_stripThickness].ToString(), fd_stripWidth, recipeTable.Rows[0][fd_stripWidth].ToString(),
                                    fd_pipeOd, recipeTable.Rows[0][fd_pipeOd].ToString(), fd_pipeThickness, recipeTable.Rows[0][fd_pipeThickness].ToString(), fd_pipeDiameter, "0", fd_yeildStrength, recipeTable.Rows[0][fd_yeildStrength].ToString(), fd_accPrPressure_1, recipeTable.Rows[0][fd_accPrPressure_1].ToString(),
                                    fd_accPrPressure_2, recipeTable.Rows[0][fd_accPrPressure_2].ToString(), fd_emCuttingSpd, recipeTable.Rows[0][fd_emCuttingSpd].ToString(), fd_emChipload, recipeTable.Rows[0][fd_emChipload].ToString(), fd_sa1Power, recipeTable.Rows[0][fd_sa1Power].ToString(), fd_sa1Temp, recipeTable.Rows[0][fd_sa1Temp].ToString(),
                                    fd_sa1Height, recipeTable.Rows[0][fd_sa1Height].ToString(), fd_sa1Freq, recipeTable.Rows[0][fd_sa1Freq].ToString(), fd_sa2Power, recipeTable.Rows[0][fd_sa2Power].ToString(), fd_sa2Temp, recipeTable.Rows[0][fd_sa2Temp].ToString(), fd_sa2Height, recipeTable.Rows[0][fd_sa2Height].ToString(),
                                    fd_sa2Freq, recipeTable.Rows[0][fd_sa2Freq].ToString(), fd_sa3Power, recipeTable.Rows[0][fd_sa3Power].ToString(), fd_sa3Temp, recipeTable.Rows[0][fd_sa3Temp].ToString(), fd_sa3Height, recipeTable.Rows[0][fd_sa3Height].ToString(), fd_sa3Freq, recipeTable.Rows[0][fd_sa3Freq].ToString(),
                                    fd_sa4Power, recipeTable.Rows[0][fd_sa4Power].ToString(), fd_sa4Temp, recipeTable.Rows[0][fd_sa4Temp].ToString(), fd_sa4Height, recipeTable.Rows[0][fd_sa4Height].ToString(), fd_sa4Freq, recipeTable.Rows[0][fd_sa4Freq].ToString(), fd_hfwLineSpeed, recipeTable.Rows[0][fd_hfwLineSpeed].ToString(),
                                    fd_HfwPower, recipeTable.Rows[0][fd_HfwPower].ToString(), fd_HfwFreq, recipeTable.Rows[0][fd_HfwFreq].ToString(), fd_HfwVee_X, recipeTable.Rows[0][fd_HfwVee_X].ToString(), fd_sustCw1, recipeTable.Rows[0][fd_sustCw1].ToString(), fd_sustCw2, recipeTable.Rows[0][fd_sustCw2].ToString(),
                                    fd_stripWdTarget, recipeTable.Rows[0][fd_stripWdTarget].ToString(), fd_HfwMode, recipeTable.Rows[0][fd_HfwMode].ToString(), fd_HfwPreHeatPower, recipeTable.Rows[0][fd_HfwPreHeatPower].ToString(), fd_HfwTemperature, recipeTable.Rows[0][fd_HfwTemperature].ToString(), fd_HfwSPCGain, recipeTable.Rows[0][fd_HfwSPCGain].ToString(),
                                    fd_HfwSPCSlope, recipeTable.Rows[0][fd_HfwSPCSlope].ToString(), fd_parameter6, "0", fd_parameter7, "0", fd_parameter8, "0", fd_parameter9, "0",
                                    fd_parameter10, "0", fd_createDate, recipeTable.Rows[0][fd_createDate].ToString(), fd_modifyDate, recipeTable.Rows[0][fd_modifyDate].ToString(), fd_recipeStatus, recipeTable.Rows[0][fd_recipeStatus].ToString(), fd_creater, recipeTable.Rows[0][fd_creater].ToString(),
                                    fd_modifyUser, recipeTable.Rows[0][fd_modifyUser].ToString(), fd_recipeNo, recipeTable.Rows[0][fd_recipeNo].ToString());
                if (OracleHelper.Exct_NonQuery(sql) > 0)
                {
                    LogHelper.WriteLog("Edit_Recipe Success");
                    return recipeTable;
                }
                else
                {
                    return null;
                }
            }
            catch (Exception ex)
            {
                LogHelper.WriteLog("Edit_Recipe", ex);
                throw ex;
            }
        }

        /// <summary>
        /// pagination query recipe table data
        /// </summary>
        /// <param name="minRow"></param>
        /// <param name="maxRow"></param>
        /// <param name="recipeTotalPages"></param>
        /// <returns></returns>
        public static DataTable Select_RecipeByIndex(int minRow, int maxRow, out string recipeTotalPages)
        {
            try
            {
                string sqlrecipe = string.Format("select * from (select ROWNUM rn,a.* from (select * from {0} order by {1} desc,{2} desc) a) where rn>{3} and rn<={4}",
                  tableName, fd_createDate, fd_modifyDate, minRow, maxRow);
                string sqlrecipecount = string.Format("select count(*) from {0}",
                  tableName);
                DataTable recipetable = OracleHelper.Exct_QueryTable(sqlrecipe);
                if (recipetable != null)
                {
                    int recipePages = Convert.ToInt32(TASK_INFO.GetTableCount(sqlrecipecount));
                    if (recipePages % pageNum == 0)
                    {
                        recipeTotalPages = (recipePages / pageNum).ToString();
                    }
                    else
                    {
                        recipeTotalPages = (recipePages / pageNum + 1).ToString();
                    }
                    LogHelper.WriteLog("Select_RecipeByIndex Success");
                    return recipetable;
                }
                else
                {
                    recipeTotalPages = "";
                    return null; ;
                }

            }
            catch (Exception ex)
            {
                recipeTotalPages = "";
                LogHelper.WriteLog("Select_RecipeByIndex", ex);
                throw ex;
            }
        }

        /// <summary>
        /// delete recipe information
        /// </summary>
        /// <param name="recipeNo"></param>
        /// <param name="userName"></param>
        /// <param name="recipeTotalPages"></param>
        /// <returns></returns>
        public static DataTable DeleteRecipeByRecipe_id(string recipeNo, string userName, out string recipeTotalPages)
        {
            try
            {
                string deleteRecipeSql = string.Format("delete from {0} where {1}='{2}'", tableName, fd_recipeNo, recipeNo);
                if (OracleHelper.Exct_NonQuery(deleteRecipeSql) > 0)
                {
                    LogHelper.WriteLog("DeleteRecipeByRecipe_id Success");
                    return Select_RecipeByIndex(0, 20, out recipeTotalPages);
                }
                else
                {
                    recipeTotalPages = "";
                    return null;
                }
            }
            catch (Exception ex)
            {
                recipeTotalPages = "";
                LogHelper.WriteLog("DeleteRecipeByRecipe_id", ex);
                throw ex;
            }
        }
    }
}
